package DAO;

import Entity.Category;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Hieu Tran
 */
public class DAO_Category {
    Connection conn = null;
    Statement state = null;
    ResultSet rs = null;

    public DAO_Category() {
        //connection1("jdbc:odbc:StudentManagementDatabase");
        connection4("jdbc:sqlserver://localhost"
                + ":1433;databaseName=StudentManagementDatabase","sa","123456");    
    }

    public final void connection1(String url) {
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            try {
                conn = DriverManager.getConnection(url);
            } catch (SQLException ex) {
                Logger.getLogger(DAO_Category.class.getName()).log(Level.SEVERE, null, ex);
            }
            System.out.println("DAO_Manager is connected");
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DAO_Category.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    public final void connection4(String url, String username, String password){
         try {
            //create driver
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            
            //get connection
            conn=DriverManager.getConnection(url, 
                    username, password);
            System.out.println("DAO category is connected");
            
        } catch (ClassNotFoundException | SQLException ex) {
        }
    }     
    public void closeConnection() {
        try {
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DAO_Category.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    /**
     * Author: HieuTD
     * @param sql
     * @return 
     */
    public ResultSet getData(String sql) {
        ResultSet dataset = null;
        try {
            state = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            dataset = state.executeQuery(sql);
        } catch (SQLException ex) {
            Logger.getLogger(DAO_Category.class.getName()).log(Level.SEVERE, null, ex);
        }

        return dataset;

    }    
    /**
     * Author: HieuTD
     * @param category
     * @return number of affected rows
     */
    public int addCategory(Category category) {
        int n = 0;
        // prepare SQL statement
        String preSQL = "INSERT INTO Category (name, slug, parentID) VALUES (?,?,?)";

        try {
            PreparedStatement pre = conn.prepareStatement(preSQL);
            pre.setString(1, category.getName());
            pre.setString(2, category.getSlug());
            if(category.getParentID() == 0) {
                pre.setNull(3, java.sql.Types.INTEGER);
            } else {
                pre.setInt(3, category.getParentID());
            }

            n = pre.executeUpdate();
        } catch (SQLException e) {
        }
        return n;
    }
    
    /**
     * Author: HieuTD
     * @return 
     */
    public List<Category> categoryList() {
        List<Category> categories = new ArrayList<>();
        try {
            String sql = "SELECT * FROM Category";
            ResultSet list = getData(sql);
            while (list.next()) {
                int id = list.getInt(1);
                String name = list.getString(2);
                String slug = list.getString(3);
                int parentID = list.getInt(4);
                Category temp = new Category(id, name, slug, parentID);
                categories.add(temp);
            }

        } catch (SQLException ex) {
            Logger.getLogger(DAO_Category.class.getName()).log(Level.SEVERE, null, ex);
        }
        return categories;
    }
    
    /**
     * Task: HieuTD
     *
     * @param id
     * @return
     */
    public int deleteCategory(int id) {
        int n = 0;
        String preSQL = "DELETE FROM Category WHERE id = " + id;

        try {
            PreparedStatement pre = conn.prepareStatement(preSQL);
            n = pre.executeUpdate();
        } catch (SQLException e) {
        }
        return n;
    }    
    /**
     * Just for test connection. Shift+F6 to run me!
     * @param args
     */
    public static void main(String[] args) {
        DAO_Category s = new DAO_Category();
    }     
}
